--- exec SP_CREATE_UPDATE_OSEP_DIEBANK_LOT '7C81500A', '4630336', '4630336', 1290, '6/25/2007', 'OSEP', 'Running', 'Die Bank', 'Prod'

ALTER PROC SP_CREATE_UPDATE_OSEP_DIEBANK_LOT (
 
 @PART_NUMBER 		VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50), 
 @FAB_LOT_NUMBER	VARCHAR(50), 
 @QTY_IN 		INT,
 @DATE_IN 		DATETIME,
 @VENDOR_NAME 		VARCHAR(50),
 @STATUS 		VARCHAR(50),
 @INVENTORY_TYPE	varchar(50), 
 @LOT_CATEGORY 		VARCHAR(50)
 )
 
 AS
 BEGIN
 
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @THIS_LOT_STATUS VARCHAR(50)
 	
	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end 	
 
 	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT, PROCESS_STEP WHERE PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT.PART_NUMBER LIKE @PART_NUMBER AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID 
 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @VENDOR_NAME
 	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
 	BEGIN
 		SELECT TOP 1 @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER LIKE @PART_NUMBER AND PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID --AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
 		IF @PROCESS_STEP_ID IS NULL
 		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @lot_number AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @inventory_type AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty, Status, TimeStamp )
					VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @inventory_type,'Discrepancy Lot Notification',  @Qty_In, 'Open', CURRENT_TIMESTAMP )
			END
			
 			
 			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
 			RETURN
 		END
		else
		begin
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
		end
 	END
 
 	SELECT @THIS_LOT_STATUS = LOT_CURRENT_STATUS, @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER

 	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, null, @STATUS, CURRENT_TIMESTAMP,1, @lot_Category, 0)
		SELECT @LOT_ID = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name,  Die_Qty, Status, TimeStamp )
				VALUES ( @part_number, @lot_number, @FAB_LOT_NUMBER, @vendor_name, @inventory_type, 'New Lot Notification',  @qty_in,  'Open', CURRENT_TIMESTAMP )
		
		INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, TIMESTAMP) VALUES (@LOT_ID, @QTY_IN, @DATE_IN, CURRENT_TIMESTAMP )		
		
	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS, QTY_IN = @QTY_IN, LOT_CATEGORY = @lot_Category, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
	
		UPDATE INVENTORY_LOT SET IL_QTY_IN = @QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
	END

	/*inserting in to sort bin release table*/
	IF @LOT_ID IS NOT NULL
	BEGIN
		EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @LOT_ID, 0, 'Bin1', @QTY_IN, 0/*rejectedqty*/, 0.0/*yield*/
	END
	
	/* Closing Transit Lots and Previous Process Lots*/
	Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @FAB_LOT_NUMBER, @QTY_IN, @DATE_IN
   		
 
 END
